package com.example.layui.util;

import com.example.layui.pojo.UserInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description //TODO
 * @Author wanghao
 * @Date 2019-08-06 19:17
 **/
public class DBUtils {

    private static String jdbc_class = "org.sqlite.JDBC";
    private static String db_path = "D:\\db\\sqlite.sqlite3";




    public static void main(String[] args) {

        queryUserInfo(1,5,"王浩");

    }
    public static List<UserInfo> queryUserInfo(Integer pageNo,Integer pageNumber,String name){
        String sql = "select * from user_info ";
        if(name!=null ){
            sql= sql +" where name = '"+name+"'";
        }
        if(pageNo!=null && pageNumber!=null){
            sql = sql +" LIMIT "+((pageNo-1)*pageNumber)+" , "+pageNumber+" ";
        }
        ResultSet rs = null;
        Connection conn = null;
        try {
            Class.forName(jdbc_class);
            conn = DriverManager.getConnection("jdbc:sqlite:" + db_path);
            Statement state = conn.createStatement();
            rs = state.executeQuery(sql); //查询数据
            List<UserInfo> list = new ArrayList<>();
            while (rs.next()) { //将查询到的数据打印出来
                UserInfo u = new UserInfo();
                u.setId(rs.getInt("id"));
                u.setAge(rs.getInt("age"));
                u.setName(rs.getString("name"));
                u.setSex(rs.getString("sex"));
                list.add(u);
            }
            return list;
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;

    }




//    public static void main(String[] args) throws SQLException {
//        try {
//            Class.forName(jdbc_class);
//            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + db_path);
//            Statement state = conn.createStatement();
//            for (int i = 0; i < 100; i++) {
//                state.executeUpdate("insert into user_info (name,age,sex)values ('王浩"+i+"',27,'男');"); //查询数据
//                state.executeUpdate("insert into user_info (name,age,sex)values ('贺鹏丽"+i+"',17,'女');"); //查询数据
//            }
//
//            conn.close();
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
//
//    }
}
